__author__ = 'sdjkx'

import os
from PyQt5.QtWidgets import QMessageBox

from libs.ImageLabelSet import ImageLabelSet

class FileInfoManager:

    def __init__(self,labelImg, mydb):
        self.mydb = mydb
        self.labelImg = labelImg
        self.insertFileSql = "insert into u_image_file(user_id,file_name,file_type,file_size,create_time,file_status) " \
                         "values('{0}','{1}',{2}, {3}, SYSDATE(),1)"
        self.queryFileSql = "select user_id, file_id,file_status from u_image_file where file_name = '{0}'"
        self.updateFileStatus = "update u_image_file set file_status={0} where user_id = '{1}' and file_name = '{2}'"
        self.queryImageLabelSql = "select id,file_id,label_id,label_count,create_time,user_id from u_image_label where file_id = {0}"
        self.updateImageLabelSql = "update u_image_label set label_count = {0},create_time=SYSDATE(),user_id='{1}' where id={2}"
        self.insertImageLabelSql = "insert into u_image_label(file_id,label_id,label_count,create_time,user_id) VALUES({0},{1},{2},SYSDATE(),{3})"
        self.insertLabelExampleSql = "insert into sys_label_img_example(label_id,file_name,user_id, img) VALUES('{0}','{1}',{2}, %s)"
        self.queryLabelExampleSql = "select label_id,file_name,user_id from sys_label_img_example where file_name = '{0}'"

    def queryFile(self, filename = ""):
        if len(filename) > 1:
            sql = self.queryFileSql.format(filename)
            results = self.mydb.executeSql(sql)
            if results != None and len(results) > 0:
                userId = results[0][0]
                fileId = results[0][1]
                fileStatus = results[0][2]
                return userId, fileId, fileStatus

        return "",0,0

    def insertFile(self, userId, filename, fileSize):
        fileType = self.getFileType(filename)
        sql = self.insertFileSql.format(userId, filename, fileType, fileSize)
        self.mydb.executeCommit(sql)

    def updateFileStatus(self, userId, fileName,fileStatus):
        sql = self.updateFileStatus.format(fileStatus, userId, fileName)
        self.mydb.executeCommit(sql)

    def getFileType(self, fileName = ""):
        if fileName.endswith("jpg"):
            return 1
        elif fileName.endswith("png"):
            return 3


    def getFileName(self, filePath):
        fileArr = str(filePath).split(os.sep)
        return fileArr[len(fileArr) - 1]


    def saveImageLabelLog(self,userId, fileId, filePath, shapeCount):
        try:
            fileName = self.getFileName(filePath)

            #  TODO 2上传本地服务器qazmxm m m   n OZ  WAZB
            # 操作类型：0下载，2上传本地服务器,3检查 4 修改，5 上传云服务器 9 删除文件
            sql = "insert into u_image_label_log(user_id,action_type,create_time,file_id,label_count,file_name) " \
                  "VALUES(" + str(userId) + ",2, SYSDATE(),"+str(fileId) +", "+ str(shapeCount) +", '"+ fileName +"')"

            self.mydb.executeCommit(sql)
        except:
            print("Error: unable to fecth data")
            return False

    def saveImageLabelToDB(self, fileId, labelId, labelCount):
        try:
            # file_id,label_name,label_count,create_time,user_id
            sql = self.insertImageLabelSql.format(fileId,labelId, labelCount, self.labelImg.userId )
            self.mydb.executeCommit(sql)

        except:
            print("Error: unable to fecth data")
            return False

    def queryImageLabelFromDB(self, fileId):
        '''
        从数据库里查询文件ID的所有标签统计数据
        :param fileId: 文件ID
        :return:
        '''
        try:
            sql = self.queryImageLabelSql.format(fileId)
            records = self.mydb.executeSql(sql)
            recordDict = {}
            if records != None:
                for item in records:
                    # id,file_id,label_name,label_count,create_time,user_id
                    imageLabelSet = ImageLabelSet()
                    imageLabelSet.id = item[0]
                    imageLabelSet.file_id = item[1]
                    imageLabelSet.label_id = item[2]
                    imageLabelSet.label_count = item[3]
                    imageLabelSet.create_time = item[4]
                    imageLabelSet.user_id = item[5]
                    recordDict[item[2]] = imageLabelSet

        except:
            print("Error: unable to fecth data")

        return {}

    def updateImageLabelToDB(self, id,labelCount):
        try:
            # "update u_image_label set labelCount = {0},create_time=SYSDATE(),user_id={1} where id={2}"
            sql = self.updateImageLabelSql.format(labelCount, self.labelImg.userId, id)
            self.mydb.executeCommit(sql)
        except:
            print("Error: 更新文件标签数量失败")

    def saveImageLabel(self,fileId,label_dict, shapes):
        labelDict = {}
        for shape in shapes:
            label = shape.label
            if label in labelDict:
                labelDict[label] = labelDict[label] + 1
            else:
                labelDict[label] = 1

        recordDict = self.queryImageLabelFromDB(fileId)

        # 更新文件标签数量
        for key in labelDict.keys():
            labelCount = labelDict[key]

            if recordDict != None:
                if key in recordDict:
                    recordDict[key].haveThis = True
                    if recordDict[key].label_count == labelCount:
                        continue
                    else:
                        id = recordDict[key].id
                        self.updateImageLabelToDB(id, labelCount)
                        continue

            # 如果是新标签，则添加一个
            label = label_dict[key]
            if label != None:
                self.saveImageLabelToDB(fileId, label_dict[key].get_id(), labelCount)
            else:
                QMessageBox.information(self.labelImg, "提示", "标签" + key + "不存在")


        # 检查已经保存到数据库里的，是否有删除的
        for key in recordDict.keys():
            if not recordDict[key].haveThis:
                id = recordDict[key].id
                self.updateImageLabelToDB(id, 0)

        return True

    def sendLabelToDB(self, labelId,fileName, filePath, userId):
        '''
        保存图片为标签示例
        :param labelId: 标签ID
        :param filePath: 图片路径
        :param userId: 用户ID
        :return:
        '''
        try:
            exampleId = self.queryImageLableExampleId(fileName)
            if exampleId > 0:
                 QMessageBox.information(self.labelImg, "提示","标签图例文件“" +fileName + "”已经存在")
                 return
            else:
                # 注意使用Binary()函数来指定存储的是二进制
                fil = open(filePath, 'rb')
                b = fil.read()
                fil.close()

                # cursor.execute("INSERT INTO demo_pic_repo SET touxiang_data= %s" % pymysql.Binary(img))
                # insert into sys_label_img_example(id,label_id,file_name,img,width,height,create_time,user_id)
                sql = self.insertLabelExampleSql.format(labelId, fileName, userId)
                self.mydb.executeParamCommit(sql , self.mydb.getBinary(b))
                # exampleId = self.queryImageLableExampleId(fileName)
                #
                # sql = self.update
                QMessageBox.information(self.labelImg, "提示","标签图例文件已经保存")
        except:
            print("Error: 更新文件标签数量失败")

    def queryImageLableExampleId(self, fileName):
        sql = self.queryLabelExampleSql.format(fileName)
        records = self.mydb.executeSql(sql)
        if records != None and len(records) > 0:
            for row in records:
                return row[0]

        return 0